import pymysql
import xlrd


def connect_mysql_and_execute_sql(sql_list):
    conn = pymysql.connect(
        host="localhost",  # 指示host表明是本地MySQL还是远程
        port=3306,
        user="root",  # 用户名
        password="root",  # 密码
        db="course",  # 要连接的数据库名
        charset="utf8mb4",  # 指定字符集，可以解决中文乱码
        cursorclass=pymysql.cursors.DictCursor
    )
    """进行语句的增删改查"""

    try:
        with conn.cursor() as cursor:
            for index, single_sql in enumerate(sql_list):
                result = cursor.execute(single_sql)  # 执行sql语句
                print('正在插入第 %s 条数据' % (index + 1))
            conn.commit()  # 提交事务
    finally:
        conn.close()  # 关闭数据库


# 要导入的excel数据， 自己定义路径就行
path = './data.xlsx'
file = xlrd.open_workbook(path)

# 索引到第一个sheet
sheet = file.sheet_by_index(0)

# 计算行数
rows = len(sheet.col_values(0))  # 计算方法先获取第一列的所有数据， 然后求长度就是有几行

# 计算列数
cols = len(sheet.row_values(0))  # 跟计算行数的方法一样

print('行数：', rows, '列数', cols)

# 要执行的sql语句列表
course_sql_list = []

"""
创建数据库表的sql语句， 表头自行修改为你想要保存的字段
"""
sql0 = 'drop table  if exists `course`'
sql00 = 'create table course (\n'
sql01 = ''
sql02 = ') engine=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci'
keys = sheet.row_values(0)

# 这里是默认excel中的第一列是id的情况
for index, key in enumerate(keys):
    if index == 0:
        sql01 += 'id int(11) primary key auto_increment'
    else:
        sql01 += ',\n%s varchar(255)' % key

# print(sql00+sql01+sql02)
course_sql_list.append(sql0)
course_sql_list.append(sql00 + sql01 + sql02)

"""
插入数据, 从第二行到最后一行
"""
for i in range(1, rows):
    row = sheet.row_values(i)
    row[0] = 'null'
    row = ['\'' + str(i) + '\'' for i in row]

    str1 = ','.join(row)
    str1 = str1.replace('\'null\'', 'null', 1)

    sql = 'insert into course values (' + str1 + ')'
    course_sql_list.append(sql)
    # print(sql)
    print(len(course_sql_list))

# 执行插入
connect_mysql_and_execute_sql(course_sql_list)
